<?php
include 'user_lib.php';
include 'lib_excel.php';

// <editor-fold defaultstate="collapsed" desc="Make Where">
$s_Where = 'where 1=1 ';
$a_user_dear = $a_user = array();

if ($s_category_team || $s_category_level || $s_category_crew || $s_category_course) {
    $db_query = "
        select  userid
        from    user_class
        where   1=1";

    if ($s_category_team        ) { $db_query .= " and team         = '$s_category_team'"       ; }
    if ($s_category_level       ) { $db_query .= " and level        = '$s_category_level'"      ; }
    if ($s_category_crew        ) { $db_query .= " and crew         = '$s_category_crew'"       ; }
    if ($s_category_course      ) { $db_query .= " and course       = '$s_category_course'"     ; }
    
    $db_result = db_query($db_conn, $db_query);

    while ($d_row = db_fetch($db_result)) {
        $a_user[$d_row->userid] = $d_row->userid;
    }

    $s_Where = $s_Where  . ' and userid in ("' . implode('", "', $a_user) . '")';

    $a_user = array();
}

make_where($s_Where, $a_filterings);
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="Get Data">
$db_query  = db_get_query($a_tablecolumns_namhoa_user) . $s_Where . " and $s_get_valid_user_condition order by updatedate desc";
$db_result = db_query($db_conn, $db_query);

while ($d_row = db_fetch($db_result)) {
    $a_user[$d_row->userid] = $d_row;
    $a_user_class[$d_row->userid] = $d_row->userid;
}

$a_user_class = get_user_class($a_user_class, array($s_category_course), $db_conn);

foreach ($a_user_class as $key => $a_course) {

    $a_course = array_pop($a_course);

    $a_user[$key]->course = $a_course->course;
    $a_user[$key]->team   = $a_course->team;
    $a_user[$key]->level  = $a_course->level;
    $a_user[$key]->crew   = $a_course->crew;
}
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="Get Dear">
$db_query  = db_get_query($a_tablecolumns_namhoa_user_dear) . " where userid in ('".implode("', '", array_keys($a_user))."')";
$db_result = db_query($db_conn, $db_query);

while ($d_row = db_fetch($db_result)) {
    $a_user_dear[$d_row->userid][] = $d_row;
}
// </editor-fold>

$v_row = 1;
$v_col = 0;

$o_excel->getActiveSheet()->setCellValueByColumnAndRow($v_col, $v_row, 'Mã Số');
$o_excel->getActiveSheet()->getStyleByColumnAndRow($v_col, $v_row)->applyFromArray($a_headerstyle);
$o_excel->getActiveSheet()->getColumnDimensionByColumn($v_col)->setWidth(20);

$v_col++;
$o_excel->getActiveSheet()->setCellValueByColumnAndRow($v_col, $v_row, 'Tên Thánh');
$o_excel->getActiveSheet()->getStyleByColumnAndRow($v_col, $v_row)->applyFromArray($a_headerstyle);
$o_excel->getActiveSheet()->getColumnDimensionByColumn($v_col)->setWidth(20);

$v_col++;
$o_excel->getActiveSheet()->setCellValueByColumnAndRow($v_col, $v_row, 'Họ Và Tên');
$o_excel->getActiveSheet()->getStyleByColumnAndRow($v_col, $v_row)->applyFromArray($a_headerstyle);
$o_excel->getActiveSheet()->getColumnDimensionByColumn($v_col)->setWidth(20);

$v_col++;
$o_excel->getActiveSheet()->setCellValueByColumnAndRow($v_col, $v_row, 'Tên');
$o_excel->getActiveSheet()->getStyleByColumnAndRow($v_col, $v_row)->applyFromArray($a_headerstyle);
$o_excel->getActiveSheet()->getColumnDimensionByColumn($v_col)->setWidth(20);

$v_col++;
$o_excel->getActiveSheet()->setCellValueByColumnAndRow($v_col, $v_row, 'Khóa');
$o_excel->getActiveSheet()->getStyleByColumnAndRow($v_col, $v_row)->applyFromArray($a_headerstyle);
$o_excel->getActiveSheet()->getColumnDimensionByColumn($v_col)->setWidth(20);

$v_col++;
$o_excel->getActiveSheet()->setCellValueByColumnAndRow($v_col, $v_row, 'Ngành');
$o_excel->getActiveSheet()->getStyleByColumnAndRow($v_col, $v_row)->applyFromArray($a_headerstyle);
$o_excel->getActiveSheet()->getColumnDimensionByColumn($v_col)->setWidth(20);

$v_col++;
$o_excel->getActiveSheet()->setCellValueByColumnAndRow($v_col, $v_row, 'Cấp');
$o_excel->getActiveSheet()->getStyleByColumnAndRow($v_col, $v_row)->applyFromArray($a_headerstyle);
$o_excel->getActiveSheet()->getColumnDimensionByColumn($v_col)->setWidth(20);

$v_col++;
$o_excel->getActiveSheet()->setCellValueByColumnAndRow($v_col, $v_row, 'Đội');
$o_excel->getActiveSheet()->getStyleByColumnAndRow($v_col, $v_row)->applyFromArray($a_headerstyle);
$o_excel->getActiveSheet()->getColumnDimensionByColumn($v_col)->setWidth(20);

$v_col++;
$o_excel->getActiveSheet()->setCellValueByColumnAndRow($v_col, $v_row, 'Ngày Sinh');
$o_excel->getActiveSheet()->getStyleByColumnAndRow($v_col, $v_row)->applyFromArray($a_headerstyle);
$o_excel->getActiveSheet()->getColumnDimensionByColumn($v_col)->setWidth(20);

$v_col++;
$o_excel->getActiveSheet()->setCellValueByColumnAndRow($v_col, $v_row, 'Điện Thoại');
$o_excel->getActiveSheet()->getStyleByColumnAndRow($v_col, $v_row)->applyFromArray($a_headerstyle);
$o_excel->getActiveSheet()->getColumnDimensionByColumn($v_col)->setWidth(20);

$v_col++;
$o_excel->getActiveSheet()->setCellValueByColumnAndRow($v_col, $v_row, 'Địa Chỉ');
$o_excel->getActiveSheet()->getStyleByColumnAndRow($v_col, $v_row)->applyFromArray($a_headerstyle);
$o_excel->getActiveSheet()->getColumnDimensionByColumn($v_col)->setWidth(20);

$v_col++;
$o_excel->getActiveSheet()->setCellValueByColumnAndRow($v_col, $v_row, 'Rửa Tội');
$o_excel->getActiveSheet()->getStyleByColumnAndRow($v_col, $v_row)->applyFromArray($a_headerstyle);
$o_excel->getActiveSheet()->getColumnDimensionByColumn($v_col)->setWidth(20);

$v_col++;
$o_excel->getActiveSheet()->setCellValueByColumnAndRow($v_col, $v_row, 'Thêm Sức');
$o_excel->getActiveSheet()->getStyleByColumnAndRow($v_col, $v_row)->applyFromArray($a_headerstyle);
$o_excel->getActiveSheet()->getColumnDimensionByColumn($v_col)->setWidth(20);

$v_col++;
$o_excel->getActiveSheet()->setCellValueByColumnAndRow($v_col, $v_row, 'Rước Lễ');
$o_excel->getActiveSheet()->getStyleByColumnAndRow($v_col, $v_row)->applyFromArray($a_headerstyle);
$o_excel->getActiveSheet()->getColumnDimensionByColumn($v_col)->setWidth(20);

$v_col++;
$o_excel->getActiveSheet()->setCellValueByColumnAndRow($v_col, $v_row, 'Giáo Họ');
$o_excel->getActiveSheet()->getStyleByColumnAndRow($v_col, $v_row)->applyFromArray($a_headerstyle);
$o_excel->getActiveSheet()->getColumnDimensionByColumn($v_col)->setWidth(20);

$v_col++;
$o_excel->getActiveSheet()->setCellValueByColumnAndRow($v_col, $v_row, 'Mã Gen - Anh/Chị Em');
$o_excel->getActiveSheet()->getStyleByColumnAndRow($v_col, $v_row)->applyFromArray($a_headerstyle);
$o_excel->getActiveSheet()->getColumnDimensionByColumn($v_col)->setWidth(20);

$v_col++;
$o_excel->getActiveSheet()->setCellValueByColumnAndRow($v_col, $v_row, 'Người Thân');
$o_excel->getActiveSheet()->getStyleByColumnAndRow($v_col, $v_row)->applyFromArray($a_headerstyle);
$o_excel->getActiveSheet()->getColumnDimensionByColumn($v_col)->setWidth(60);

foreach ($a_user as $s_ID => $o_UserInfo) {

    $v_row++;
    $v_col = 0;

    $v_tmp_team  = isset($a_Nganh  [$o_UserInfo->team ]) ? $a_Nganh[$o_UserInfo->team ] : '';
    $v_tmp_level = isset($a_Cap    [$o_UserInfo->level]) ? $a_Cap  [$o_UserInfo->level] : '';
    $v_tmp_crew  = isset($a_Doi    [$o_UserInfo->crew ]) ? $a_Doi  [$o_UserInfo->crew ] : '';

    $o_excel->getActiveSheet()->setCellValueByColumnAndRow($v_col, $v_row, $s_ID);

    $v_col++;
    $o_excel->getActiveSheet()->setCellValueByColumnAndRow($v_col, $v_row, $o_UserInfo->saint);

    $v_col++;
    $o_excel->getActiveSheet()->setCellValueByColumnAndRow($v_col, $v_row, $o_UserInfo->username);

    $v_col++;
    $o_excel->getActiveSheet()->setCellValueByColumnAndRow($v_col, $v_row, $o_UserInfo->firstname);

    $v_col++;
    $o_excel->getActiveSheet()->setCellValueByColumnAndRow($v_col, $v_row, $o_UserInfo->course);

    $v_col++;
    $o_excel->getActiveSheet()->setCellValueByColumnAndRow($v_col, $v_row, $v_tmp_team);

    $v_col++;
    $o_excel->getActiveSheet()->setCellValueByColumnAndRow($v_col, $v_row, $v_tmp_level);

    $v_col++;
    $o_excel->getActiveSheet()->setCellValueByColumnAndRow($v_col, $v_row, $v_tmp_crew);

    $v_col++;
    $o_excel->getActiveSheet()->setCellValueByColumnAndRow($v_col, $v_row, $o_UserInfo->birthday);

    $v_col++;
    if (!$o_UserInfo->mobile || !$o_UserInfo->phone) {
        $o_excel->getActiveSheet()->setCellValueByColumnAndRow($v_col, $v_row, $o_UserInfo->mobile . $o_UserInfo->phone);
    } else {
        $o_excel->getActiveSheet()->setCellValueByColumnAndRow($v_col, $v_row, $o_UserInfo->mobile . "\n" . $o_UserInfo->phone);
        $o_excel->getActiveSheet()->getStyleByColumnAndRow($v_col, $v_row)->getAlignment()->setWrapText(true);
    }

    $v_col++;
    $o_excel->getActiveSheet()->setCellValueByColumnAndRow($v_col, $v_row, $o_UserInfo->address);

    $v_col++;
    $o_excel->getActiveSheet()->setCellValueByColumnAndRow($v_col, $v_row, namhoa_date($o_UserInfo->baptism_date));

    $v_col++;
    $o_excel->getActiveSheet()->setCellValueByColumnAndRow($v_col, $v_row, namhoa_date($o_UserInfo->confirmation_date));

    $v_col++;
    $o_excel->getActiveSheet()->setCellValueByColumnAndRow($v_col, $v_row, namhoa_date($o_UserInfo->eucharist_date));

    $v_col++;
    $o_excel->getActiveSheet()->setCellValueByColumnAndRow($v_col, $v_row, $o_UserInfo->parish);

    $v_col++;
    $o_excel->getActiveSheet()->setCellValueByColumnAndRow($v_col, $v_row, $o_UserInfo->gencode);

    if (isset($a_user_dear[$s_ID])) {
        
        $v_col++;
        $aTempStr = array();
        foreach ($a_user_dear[$s_ID] as $o_DearInfo) {
            $aTempStr[] = @$a_MoiQuanHe[$o_DearInfo->relationship] . ": $o_DearInfo->saint $o_DearInfo->dearname - $o_DearInfo->mobile | $o_DearInfo->phone - $o_DearInfo->address";
        }

        $o_excel->getActiveSheet()->setCellValueByColumnAndRow($v_col, $v_row, implode("\n", $aTempStr));
        $o_excel->getActiveSheet()->getStyleByColumnAndRow($v_col, $v_row)->getAlignment()->setWrapText(true);
    }
}

$o_excel->getActiveSheet()->setTitle('Danh_Sach');

header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="Danh_Sach_Doan_TNTT_Nam_Hoa.xls"');
header('Cache-Control: max-age=0');

$o_writer->save('php://output');
?>